A spreadsheet is a computer application displaying rectangular table (or grid) of information, consisting of text and numbers, for example financial information. A spreadsheet may furthermore contain formulas that give rules for computing certain values in the table from other values, such as the total value of a column, which is computed by adding the values above it.
The advent of advanced web technologies, such as Ajax and XUL, has propelled the emergence of a new generation of online spreadsheets. Equipped with a rich Internet application user experience. Many of the web based online spreadsheets boast the same features seen in desktop spreadsheet applications. Some already surpass them, offering real time updates from remote sources such as stock prices and currency exchange rates.
Google Docs is a free, Web-based word processor, spreadsheet, and presentation application offered by Google. It allows users to create and edit documents online while collaborating in real-time with other users.
Various Web services are available to users. Web services are defined by the World Wide Web Consortium (W3C) as software systems designed to support interoperable Machine to Machine interaction over a network. Web services are frequently just Web APIs that can be accessed over a network, such as the Internet, and executed on a remote system hosting the requested services. The W3C Web service definition encompasses many different systems, but in common usage the term refers to clients and servers that communicate using XML messages that follow the SOAP standard. Common in both the field and the terminology is the assumption that there is also a machine readable description of the operations supported by the server written in the Web Services Description Language (WSDL).
The data provided by various web services is tabular data, or may be converted to tabular format using the description provided. For example, Google Finance is a web service providing stock information and other financial data of worldwide companies and Google Analytics is a web service providing information on traffic on a user's website.
Other significant tabular data sources are various report generators such as SQL queries, Crystal Reports, Business Objects and others.
A pivot table is an interactive table that automatically extracts, organizes, and summarizes data. It can be used to analyze the data, make comparisons, detect patterns and relationships, and discover trends as well as other data related operations and discovery.
Today, many applications provide pivot tables or equivalent functionality, but the best-known modern implementation of the concept is in the dominant spreadsheet application, Microsoft Excel. A pivot table can be graphically represented in a pivot chart.
For typical data entry and storage, data are usually flat. Flat means that it consists of only columns and rows, such as shown in FIG. 1.
While there is a lot of information stored in such data, it is very difficult to gather the information you want out of it. A pivot table can help one quickly summarize the flat data, giving it depth, and get the information they want. The usage of a pivot table is extremely broad and depends on the situation. The first question to ask is, “what am I looking for?”. In the example here, let's ask “How many Units did we sell in each country for every Ship Date?”. The resulting pivot table is shown in FIG. 2.
A pivot table usually consists of rows, columns, and data (or fact) fields. In this case, the row is Country, the column is Ship Date, and the data we would like to see is Units. These fields were added onto the pivot table from a list of available fields. Pivot tables also allow several kinds of aggregations including: sum, average, standard deviation, count, etc. Rows and columns can be nested to provide Cartesian multiplication of two or more dimensions.
Using the example above, it will find all distinct records for Country. In this case, they are: USA, France, Germany, UK and Israel. Furthermore, it will find all distinct records for Ship Date.
Existing tools for creating pivot tables from spreadsheets have several disadvantages compared to tools for creating pivot tables from multi-dimensional databases. For example:                1. All dimensions (columns) in a pivot table created from a spreadsheet appear as flat, thus hierarchies cannot be created, such as a year\quarter\months hierarchy from a date column, or a Country\State\City\Customers hierarchy.        2. Calculation that are created on top of the spreadsheet pivot table are Excel calculations, i.e. they are based on R1C1 convention, while OLAP relates to entities (members), i.e. in Excel one would do A6+B6, while in OLAP one could do Q1+Q2.        3. Columns do not ‘tell’ what they are while dimension do (i.e. Time dimension Vs. Col 6).        4. OLAP can work on larger amounts of data than e.g. Microsoft Excel Pivot can provide        
US Published Application No. 2007/0260578, assigned to Microsoft Corp., discloses a method and apparatus that allow the user to view advanced data-base pivot tables in spreadsheets, from any relational database, by creating a temporary cube on the local machine from the relational database. The pivot tables interact with the temporary cube to provide the data to the user.
U.S. Pat. No. 7,213,199, assigned to Cognos Inc., discloses an extension to a conventional spreadsheet application, for interacting with multidimensional databases. Instances of the extended spreadsheet application execute on a client computer and present a user with a two-dimensional representation of a portion of the multi-dimensional database.
Thus there is need for a method and system for creating pivot tables from spreadsheets, or any other tabular data source, e.g. SQL server tables or Web services or RDB based reports, whereby multi-dimensional capabilities may be provided.